/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package wassalt.Utils;

import org.json.JSONArray;
import org.json.JSONObject;

import wassalt.Utils.AppConstants;

import android.content.ContentValues;
import android.content.Context;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class DbAdapter {

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;
	public boolean isOpened = false;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		/**
		 * Creates a SQLLiteOpenHelper of type DatabaseHelper
		 */
		DatabaseHelper(Context context) {
			super(context, AppConstants.DATABASE_NAME, null,
					AppConstants.DATABASE_VERSION);
		}

		/**
		 * Creates the database tables in the SQLLite Database
		 * @param db
		 *        The SQLLite Database in which the tables will be created
		 *            
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(AppConstants.DATABASE_CREATE_IN);
			db.execSQL(AppConstants.DATABASE_CREATE_OUT);
			db.execSQL(AppConstants.DATABASE_CREATE_SELF);
			db.execSQL(AppConstants.FAVORITE_CLIENTS);
            db.execSQL(AppConstants.FAVORITE_NOTIFICATIONS);
		}

		/**
		 * @param db
		 *            The database to be upgraded
		 * @param oldVersion
		 *            The version of the current database
		 * @param newVersion
		 *            The version of the new database
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w("Database", "Upgrading database from version " + oldVersion
					+ " to " + newVersion + ", which will destroy all old data");
			db.execSQL(AppConstants.DATABASE_DROP_IN);
			db.execSQL(AppConstants.DATABASE_DROP_OUT);
			db.execSQL(AppConstants.DATABASE_DROP_SELF);
			db.execSQL(AppConstants.DATABASE_DROP_CLIENTS);
            db.execSQL(AppConstants.DATABASE_DROP_NOTIFICATIONS);
			onCreate(db);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            The context within which to work
	 */
	public DbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the local database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public DbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		isOpened = true;
		return this;
	}

	/**
	 * Closes the database
	 */
	public void close() {
		mDbHelper.close();
		isOpened = false;
	}

	/**
	 * @author Aya Kamel
	 * @param in
	 *            The id of the current tab
	 * @return The name of the table corresponding to this tab
	 */
	public String getTable(int in) {
		String name = "";
		switch (in) {
		case AppConstants.TAB_INCOMING:
			name = AppConstants.IN_TABLE;
			break;
		case AppConstants.TAB_OUTGOING:
			name = AppConstants.OUT_TABLE;
			break;
		case AppConstants.TAB_SELF:
			name = AppConstants.SELF_TABLE;
			break;
		}
		return name;
	}

	/**
	 * @author Aya Kamel
	 * @param string
	 *            The notification id
	 * @param in
	 *            Determines which  execute on the incoming or outgoing table
	 * @return whether the notification exists in the database
	 */
	protected Cursor exists(String string, int in) {

		String query = "SELECT id as _id FROM " + getTable(in) + " WHERE "
				+ AppConstants.NOT_ID + " = '" + string + "'";
		return mDb.rawQuery(query, null);
	}
	
	/**
	 * Extracts a certain field from a JSONObject
	 * @author Aya Kamel
	 * @param field 
	 * 			The field that will be extracted
	 * @param obj 
	 * 			The object from which the field will be extracted
	 * @return 
	 * 			The object represented the extracted field
	 */
	public String extract(String field, JSONObject obj)
	{
		String output = null;
		try
		{
			output = obj.getString(field);
		}
		catch(Exception e){
		}
		return output;
	}

	/**
	 * Creates an object of the input parameters to be inserted/updated
	 * in the database
	 * @author Aya Kamel
	 * @return The object containing all the parameters
	 *         
	 */
	public ContentValues createContentValues(JSONObject object, int tab) {
		ContentValues initialValues = new ContentValues();
		
		JSONObject obj;
		JSONObject recv;
		String names = "";
		String phones = "";
		
		if(tab == AppConstants.TAB_OUTGOING){
			//Extract the notification and receivers data from the object
			try{
				obj = object.getJSONObject("notif");
				recv = object.getJSONObject("nrd");
				names = getNames(recv);
				phones = getPhones(recv);
			}
			catch(Exception e)
			{
				obj = null;
				recv = null;
				Log.e("Exception", e.getMessage());
			}
		}
		else
		{
			obj = object;
			recv = null;
			names = extract("name", obj);
			phones = extract("phone", obj);
		}
		
		if(tab != AppConstants.TAB_SELF)	
		{
			//and fill in the non-self tab entries
			initialValues.put(AppConstants.CNAME, names);
			initialValues.put(AppConstants.CPHONE, phones);
			initialValues.put(AppConstants.STATUS, extract("status", obj));
			String ntype = extract("ntype", obj);
			boolean requested = true;
			if(ntype.equals("1")) //request
				requested = true;
			else
				requested = false;
			initialValues.put(AppConstants.NOT_TYPE, requested);
		}
		else
		{
			//use the same object from the input
			obj = object;
			recv = null;
		}
		
		//Fill in the common entries from the obj object
		initialValues.put(AppConstants.LOCATION, extract("place", obj));
		initialValues.put(AppConstants.VALUE, extract("when", obj));
		initialValues.put(AppConstants.TYPE, extract("type", obj));
		initialValues.put(AppConstants.START, extract("startDate", obj));
		initialValues.put(AppConstants.END, extract("endDate", obj));
		initialValues.put(AppConstants.NOT_ID, extract("req_id", obj));
		return initialValues;
	}

	/**
	 * Gets the phone numbers that will receive/sent the request
	 * @param obj
	 * 		The object containing the numbers
	 * @return
	 * 		A string containing concatenated numbers
	 * @author Aya Kamel
	 */
	private String getPhones(JSONObject obj) {
		try
		{
			JSONArray receivers = new JSONArray(obj.getString("myArrayList"));
			String phones = "";
			for(int i=0; i<receivers.length(); i++)
			{
				JSONObject jsonObject = receivers.getJSONObject(i);
				phones += jsonObject.getString("receiver");
			}
			return phones;
		}
		catch(Exception e)
		{
			return null;
		}
	}

	/**
	 * Gets the names of the recipients from the recipients objects
	 * @param obj
	 * 		The object containing the recipients data
	 * @return
	 * 		A string of recipient names
	 * @author Aya Kamel
	 */
	private String getNames(JSONObject obj) {
		
		try
		{
			JSONArray receivers = new JSONArray(obj.getString("myArrayList"));
			String names = "";
			for(int i=0; i<receivers.length(); i++)
			{
				JSONObject jsonObject = receivers.getJSONObject(i);
				String fname = jsonObject.getString("firstname");
				String lname = jsonObject.getString("lastname");
				String phn = jsonObject.getString("receiver");
				if(!fname.equals("null"))
				{
					names += fname+ " "+lname;
				}
				else
				{
					names += phn;
				}
			}
			return names;
		}
		catch(Exception e)
		{
			return null;
		}
	}

	/**
	 * Creates a notification and stores it in the database
	 * @author Aya Kamel
	 * @param obj
	 *            JSONObject holding all notification data
	 * @return -1 if the insertion failed 
	 */
	public long createNotification(JSONObject obj, int tab) {

		String not_id = "";
		//Get the request id from the object
		try{
			if(tab != AppConstants.TAB_OUTGOING){
				not_id = obj.getString("req_id");
			}
			else{
				JSONObject notif = obj.getJSONObject("notif");
				not_id = notif.getString("req_id");
			}
		}
		catch(Exception e)
		{
			Log.e("Exception", e.getMessage());
		}
		
		//Check if the notification already exists in the table
		Cursor mCursor = exists(not_id, tab);
		
		//if it doesn't exist in the table insert it else update it
		if (mCursor == null || mCursor.getCount() == 0)
		{
			return mDb.insert(getTable(tab), null, createContentValues(obj, tab));
		}
		else 
		{
			String rowId = mCursor.getString(mCursor.getColumnIndex(AppConstants.ID));
			return mDb.update(getTable(tab), createContentValues(obj, tab),
					AppConstants.ID + "=" + rowId, null);
		}
	}

	/**
	 * Inserts a new notification knowing its parameters one by one
	 * @param req_id Id of the request on the server
	 * @param name The other side's name(s)
	 * @param status Status of the notification
	 * @param requested Whether it is requested/offered
	 * @param place The location of the notification
	 * @param value The duration/distance
	 * @param type Duration/distance based
	 * @param start Start time
	 * @param end End time
	 * @param tab The table to be inserted in
	 * @author Aya Kamel
	 */
	public void insertNotification(String req_id, String name, String status,
			boolean requested, String place, String value, String type,
			String start, String end, int tab, String phone)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(AppConstants.CNAME, place);
		initialValues.put(AppConstants.CPHONE, phone);
		initialValues.put(AppConstants.STATUS, status);
		initialValues.put(AppConstants.NOT_TYPE, requested);
		initialValues.put(AppConstants.LOCATION, place);
		initialValues.put(AppConstants.VALUE, value);
		initialValues.put(AppConstants.TYPE, type);
		initialValues.put(AppConstants.START, start);
		initialValues.put(AppConstants.END, end);
		initialValues.put(AppConstants.NOT_ID, req_id);
		mDb.insert(getTable(tab), null, initialValues);
	}
	
	/**
	 * Resets all the tables in the database
	 * @author Aya Kamel 
	 */
	public void resetTables() {
		mDb.execSQL(AppConstants.DATABASE_DELETE_IN);
		mDb.execSQL(AppConstants.DATABASE_DELETE_OUT);
		mDb.execSQL(AppConstants.DATABASE_DELETE_SELF);
	}
	
	/**
	 * Resets incomings table
	 * @author Aya Kamel
	 */
	public void clearIncoming()
	{
		mDb.execSQL(AppConstants.DATABASE_DELETE_IN);
	}
	
	/**
	 * Resets outgoings table
	 * @author Aya Kamel
	 */
	public void clearOutgoing()
	{
		mDb.execSQL(AppConstants.DATABASE_DELETE_OUT);
	}
	
	/**
	 * Resets self table
	 * @author Aya Kamel
	 */
	public void clearSelf()
	{
		mDb.execSQL(AppConstants.DATABASE_DELETE_SELF);
	}

	/**
	 * Delete the notification with the given rowId
	 * @author Aya Kamel
	 * @param in
	 *           Determines which type of entry to execute on	 
	 * @param rowId
	 *           id of note to delete
	 * @return true if deleted, false otherwise 
	 */
	public boolean deleteNotification(long rowId, int in) {

		Cursor c = fetchNotification(rowId, in);
		String req_id = c.getString(c.getColumnIndex(AppConstants.NOT_ID));

		ClientServerCommunication.deleteRequestFromServer(req_id, mCtx, rowId, in);
		//return mDb.delete(getTable(in), AppConstants.ID + "=" + rowId, null) > 0;
		return true;
	}
	
	/**
	 * Removes the notification in rowId from the local database
	 * @param rowId
	 * 		The id of the notification
	 * @param in
	 * 		The table in which the notification exists
	 * @return
	 * 		true of deletion was successful, false otherwise
	 * @author Aya Kamel
	 */
	public boolean removeLocally(long rowId, int in)
	{
		return mDb.delete(getTable(in), AppConstants.ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Cancels the notification with the given rowId
	 * @author Aya Kamel
	 * @param in
	 *           Determines which type of entry to execute on	 
	 * @param rowId
	 *           id of note to delete
	 * @return true if cancelled, false otherwise 
	 */
	public boolean cancelNotification(long rowId, int in) {

		Cursor c = fetchNotification(rowId, in);
		if(c != null && c.getCount()>0)
		{
			String req_id = c.getString(c.getColumnIndex(AppConstants.NOT_ID));
			ClientServerCommunication.cancelRequestFromServer(req_id, mCtx, rowId, in);
			return true;
		}
		return false;
	}

	/**
	 * Fetches a notification from the specified table by id
	 * @author Aya Kamel
	 * @param rowId
	 *            The id of the notification
	 * @param in
	 *            The table in which the notification should exist
	 * @return The cursor pointing to the notification instance
	 */
	public Cursor fetchNotification(long rowId, int in) {

		Cursor mCursor;
		mCursor = mDb.rawQuery("SELECT id as _id,* from " + getTable(in)
				+ " where " + AppConstants.ID + " = " + rowId, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public long rowIdByReqId(long rowId, int in) {

		Cursor mCursor = mDb.rawQuery("SELECT id as _id,* from " + getTable(in)
				+ " where " + AppConstants.NOT_ID + " = " + rowId, null);

		if (mCursor != null && mCursor.getCount() > 0) {
			mCursor.moveToFirst();
			return mCursor.getLong(mCursor.getColumnIndex(AppConstants.ID));
		}
		else
			return -1;
	}

	/**
	 * Gets a cursor pointing to the list of all notifications in the database
	 * @author Aya Kamel
	 * @param in
	 *           Determines whether execute on the incoming or outgoing table
	 * @return Cursor over all notifications
	 */
	public Cursor fetchAllNotifications(int tab_id) {

		Cursor mCursor;
		mCursor = mDb.rawQuery("SELECT id as _id,* from " + getTable(tab_id),
				null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	/**
	 * Determines whether a specific outgoing notification is requested or offered
	 * @author Aya Kamel
	 * @param id
	 *            The id of the notification
	 * @return true if it is requested and false if it is offered 
	 */
	public boolean isRequested(long id) {

		Cursor c = fetchNotification(id, AppConstants.TAB_OUTGOING);
		return c.getString(c.getColumnIndex(AppConstants.NOT_TYPE)).equals("1");
	}

	/**
	 * Determines the status of an input outgoing notification
	 * @author Aya Kamel
	 * @param id
	 *            The id of the notification to be checked
	 * @return true if the notification is accepted and false it it is rejected
	 *         
	 */
	public boolean isAccepted(long id) {

		Cursor c = fetchNotification(id, AppConstants.TAB_OUTGOING);
		return c.getString(c.getColumnIndex(AppConstants.STATUS)).equals(
				AppConstants.ACCEPTED);
	}

	/**
	 * Service call to update the status of request with id to b
	 * @author Aya Kamel
	 * @param id
	 *            The id of the notification to be edited
	 * @param b
	 *            true if accepted and false if rejected Initiates an HTTP
	 *            
	 */
	public void setStatus(long id, boolean b) {
		// Get the server id of the notification selected
		Cursor c = fetchNotification(id, AppConstants.TAB_OUTGOING);
		String req_id = c.getString(c.getColumnIndex(AppConstants.NOT_ID));
		ClientServerCommunication.setStatus(req_id, b, mCtx, id);
	}
	
	/**
	 * Changes the local status of notification of id to b
	 * @param id
	 * 		The id of the notification to be updated
	 * @param b
	 * 		true for Accepted and false for Rejected
	 * @author Aya Kamel
	 */
	public void setLocalStatus(long id, boolean b)
	{
		ContentValues cv = new ContentValues();
		if(b)
			cv.put(AppConstants.STATUS, "Accepted");
		else
			cv.put(AppConstants.STATUS, "Rejected");
		 mDb.update(getTable(AppConstants.TAB_OUTGOING), 
				 cv, AppConstants.ID + "=" + id, null);
	}
	
	/**
	 * @author Aya Kamel
	 * @param name The name of the contact
	 * @param phone The phone number of the contact
	 * @return object containing the contacts details
	 * Creates an object in order to insert it in the database
	 */
	public ContentValues createContact(String name, String phone)
    {
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(AppConstants.CNAME, name);
        initialValues.put(AppConstants.CPHONE, phone);
        return initialValues;
    }
    
	/**
	 * @author Aya Kamel
	 * @param name The name of the contact
	 * @param phone The phone number of the contact
	 * Adds a favorite contact to the database
	 */
    public long addContact(String name, String phone) {
    	
    	Cursor mCursor = contactExists(phone);
    	if(mCursor == null || mCursor.getCount() == 0)
    	   return mDb.insert(AppConstants.CONTACTS_TABLE, null, 
	        		createContact(name, phone));
    	else
    		return AppConstants.ALREADY_EXISTS;
    }
	
    /**
     * @author Aya Kamel
     * @param phone The phone number of the contact
     * @return The contact whose phone number is phone (if exists)
     */
	private Cursor contactExists(String phone) {
		
		String query = "SELECT id as _id FROM "+AppConstants.CONTACTS_TABLE
    			+" WHERE "+AppConstants.CPHONE+" = '"+phone+"'";
    	return mDb.rawQuery(query, null);
	}
	
	/**
	 * @author Aya Kamel
	 * @param name The name of the other client
	 * @param phone The phone number of the other client
	 * @param location The location of the notification
	 * @param value The time/distance to arrival
	 * @param type The notification type time-based/distance-based
	 * @param in The notification type incoming/outgoing
	 * Adds a favorite notification in the database
	 */
	public long addNotification(String name, String phone, String location,
			String value, String type, int in) {
    	
    	Cursor mCursor = notificationExists(phone, location, value, type, in);
    	if(mCursor == null || mCursor.getCount() == 0)
    	{
	       return mDb.insert(AppConstants.NOTIFICATIONS_TABLE, null, 
	        		createNotification(name, phone, location, value, type, in));
    	}
    	else
    	{
    		return AppConstants.ALREADY_EXISTS;	
        }
    }
	
	/**
	 * @author Aya Kamel
	 * @param name The name of the other client
	 * @param phone The phone number of the other client
	 * @param location The location of the notification
	 * @param value The time/distance to arrival
	 * @param type The notification type time-based/distance-based
	 * @param in The notification type incoming/outgoing
	 * Creates an object of the input parameters to insert it in the database
	 */
	private ContentValues createNotification(String name, String phone,
			String location, String value, String type, int in) {
		
		ContentValues initialValues = new ContentValues();
    	initialValues.put(AppConstants.CNAME, name);
        initialValues.put(AppConstants.CPHONE, phone);
        initialValues.put(AppConstants.LOCATION, location);
        initialValues.put(AppConstants.VALUE, value);
        initialValues.put(AppConstants.TYPE, type);
        initialValues.put(AppConstants.NOT_TYPE, in);
        return initialValues;
	}

	/**
	 * @author Aya Kamel
	 * @param phone The phone number of the other client
	 * @param location The location of the notification
	 * @param value The time/distance to arrival
	 * @param type The notification type time-based/distance-based
	 * @param in The notification type incoming/outgoing
	 * @return The notification having the same input parameters (if exists)
	 */
	private Cursor notificationExists(String phone,
			String location, String value, String type, int in) {
		
		String query = "SELECT id as _id FROM "+AppConstants.NOTIFICATIONS_TABLE
    			+" WHERE "+AppConstants.CPHONE+" = '"+phone
    			+"' AND "+AppConstants.LOCATION+" = '"+location
    			+"' AND "+AppConstants.VALUE+" = '"+value
    			+"' AND "+AppConstants.TYPE+" = '"+type
    			+"' AND "+AppConstants.NOT_TYPE+" = '"+in+"'";
    	return mDb.rawQuery(query, null);
	}

	/**
	 * @author Aya Kamel
	 * @param rowId The contact id
	 * Deletes a contact from the favorites database
	 */
	public boolean deleteContact(long rowId) {
		boolean res = mDb.delete(AppConstants.CONTACTS_TABLE, AppConstants.ID + "=" + rowId, null) > 0;
		Log.d("Tracer", "Deleting "+rowId+" success: "+res);
    	return res;
    }
	
	/**
	 * @author Aya Kamel
	 * @param rowId The notification id
	 * Deletes a notification from the favorites database
	 */
	public boolean deleteNotification(long rowId) {
    	return mDb.delete(AppConstants.NOTIFICATIONS_TABLE, AppConstants.ID + "=" + rowId, null) > 0;
    }
	
	/**
	 * @author Aya Kamel
	 * @param id The contact id
	 * Gets a contact from the database by his/her id
	 */
	public Cursor getContact(String phone)
	{
		Cursor mCursor;
        mCursor = mDb.rawQuery("SELECT id as _id,* from "+AppConstants.CONTACTS_TABLE
        		+" where "+AppConstants.CPHONE+" LIKE '"+phone+"'", null);
        
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
	}
	
	/**
	 * @author Aya Kamel
	 * @param id The notification id
	 * Gets a notification from the database by its id
	 */
	public Cursor NotificationID(long id)
	{
		Cursor mCursor;
        mCursor = mDb.rawQuery("SELECT id as _id,* from "+AppConstants.NOTIFICATIONS_TABLE
        		+" where "+AppConstants.ID+" = "+id, null);
        
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
	}

	/**
	 * @author Aya Kamel
	 * Fetches all contacts on the favorites list
	 */
	public Cursor AllContacts() {
		Cursor mCursor;
        mCursor = mDb.rawQuery("SELECT id as _id,* from "+AppConstants.CONTACTS_TABLE, null);
        if (mCursor != null) {
        	mCursor.moveToFirst();
        }
        return mCursor;
	}
	
	/**
	 * @author Aya Kamel
	 * @param in true for incoming notifications and false for outgoing notifications
	 * Fetches all notifications on the favorites list based on whether they are incoming or outgoing
	 */
	public Cursor AllNotifications() {
		Cursor mCursor;
        mCursor = mDb.rawQuery("SELECT id as _id,* from "+AppConstants.NOTIFICATIONS_TABLE, null);
        
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
	}

	/**
	 * A filtered version of all contacts
	 * @param string
	 * 		The string used for filtering
	 * @return All contacts whose names contain string
	 * @author Aya Kamel
	 */
	public Cursor AllContacts(String string) {
		Cursor mCursor;
        mCursor = mDb.rawQuery("SELECT id as _id,* from "+AppConstants.CONTACTS_TABLE+" where "+AppConstants.CNAME+" LIKE '%"+string+"%'", null);
        if (mCursor != null) {
        	mCursor.moveToFirst();
        }
        return mCursor;
	}

	/**
	 * A filtered version of all notifications
	 * @param string
	 * 		The string used for filtering
	 * @return All notifications where the contact name contains string
	 * @author Aya Kamel
	 */
	public Cursor AllNotifications(String string) {
		Cursor mCursor;
        mCursor = mDb.rawQuery("SELECT id as _id,* from "+AppConstants.NOTIFICATIONS_TABLE+" where "+AppConstants.CNAME+" LIKE '%"+string+"%'", null);
        if (mCursor != null) {
        	mCursor.moveToFirst();
        }
        return mCursor;
	}
	
	public void clearFavorites()
	{
		mDb.execSQL(AppConstants.DATABASE_DELETE_CONTACTS);
		mDb.execSQL(AppConstants.DATABASE_DELETE_NOTIFICATIONS);
	}
	
}
